using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using Metro.Admin.BusinessEntities;
using Microsoft.Practices.EnterpriseLibrary.Data;   

namespace Metro.Admin.DataAccess
{
    partial class AdminDataSource
    {

        private List<Country> countries=null;


        public List<Country> GetCountries()
        {
            string id = string.Empty;
            string name = string.Empty;

            try
            {
                countries = new List<Country>();
                string sqlCommand = "SELECT [id],[name] FROM [Country]";
                DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                using (IDataReader dataReader = metroDb.ExecuteReader(dbCommand))
                {
                    while (dataReader.Read())
                    {
                        id = dataReader["id"].ToString();
                        name = dataReader["name"].ToString();

                        countries.Add(new Country(id, name));
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return countries;
        }
        public void UpdateCountry(Country country)
        {
            try
            {
                string sqlCommand = "UPDATE [Country] SET [name] = @name WHERE [id] = @id";
                DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                metroDb.AddInParameter(dbCommand, "@id", DbType.String, country.Id);
                metroDb.AddInParameter(dbCommand, "@name", DbType.String, country.Name);
                metroDb.ExecuteNonQuery(dbCommand);

            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        public void CreateCountry(Country country)
        {
            try
            {
                string sqlCommand = "INSERT INTO [Country] ([id],[name]) VALUES(@id,@name)";
                DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                metroDb.AddInParameter(dbCommand, "@id", DbType.String, country.Id);
                metroDb.AddInParameter(dbCommand, "@name", DbType.String, country.Name);
                metroDb.ExecuteNonQuery(dbCommand);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteCountry(Country country)
        {
            try
            {
                string sqlCommand = "DELETE FROM [Country] WHERE[id] = @id";
                DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                metroDb.AddInParameter(dbCommand, "@id", DbType.String, country.Id);
                metroDb.ExecuteNonQuery(dbCommand);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public Country GetCountry(string idCountry)
        {
            string id = string.Empty;
            string name = string.Empty;
            Country retCountry=null;

            try
            {
                string sqlCommand = "SELECT [id],[name] FROM [Country] WHERE id=@id";
                DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                metroDb.AddInParameter(dbCommand, "@id", DbType.String, idCountry);
                using (IDataReader dataReader = metroDb.ExecuteReader(dbCommand))
                {
                    while (dataReader.Read())
                    {
                        id = dataReader["id"].ToString();
                        name = dataReader["name"].ToString();

                        retCountry = new Country(id, name);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return retCountry;
        }
        
    }
}
